/**
 * 
 */
package com.point.business.database.managers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import com.point.business.database.model.History;
import com.point.business.database.model.User;
import com.point.business.database.util.DatabaseConnection;
import com.point.business.util.Role;
import com.point.business.util.UserNotFoundException;

/**
 * @author Ilija Trajkoski
 * 
 */
public class UsersManager {
	private static UsersManager instance = new UsersManager();
	private static Logger logger = Logger.getLogger(UsersManager.class);

	private UsersManager() {
	}

	public static UsersManager getInstance() {
		return instance;
	}

	public User login(String username, String password)
			throws UserNotFoundException {
		User user = null;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("select * from users where username=?");
			statement.setString(1, username);
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				user = new User();
				createUserObject(user, resultSet);
				updateUserLoginTimeStamp(user.getId());
			}
			resultSet.close();
			if (password == null) {
				throw new UserNotFoundException("passwordNotEmpty");
			}
			if (user != null) {
				if (!user.getPassword().equals(password)) {
					throw new UserNotFoundException("incorrectPassword");
				}
			} else {
				throw new UserNotFoundException("userNotFound");
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return user;
	}

	public boolean testConnection() throws UserNotFoundException {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement("SELECT NOW()");
			ResultSet resultSet = statement.executeQuery();
			if (!resultSet.next()) {
				resultSet.close();
				return false;
			} else {
				resultSet.close();
				return true;
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return true;
	}

	public List<User> getAllUsers() {
		List<User> result = new ArrayList<User>();
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement("select * from users");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				User user = new User();
				createUserObject(user, resultSet);
				result.add(user);
			}
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public User getUserById(int id) {
		User user = new User();
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement("select * from users where id=?");
			statement.setInt(1, id);
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				createUserObject(user, resultSet);
			}
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return user;
	}

	/**
	 * @param user
	 * @param resultSet
	 * @throws SQLException
	 */
	private void createUserObject(User user, ResultSet resultSet)
			throws SQLException {
		user.setId(resultSet.getInt("id"));
		user.setEmail(resultSet.getString("email"));
		user.setFullName(resultSet.getString("fullName"));
		user.setPassword(resultSet.getString("password"));
		user.setRole(Role.valueOf(resultSet.getString("role")));
		user.setUrl(resultSet.getString("url"));
		user.setUsername(resultSet.getString("username"));
		user.setMaxSearchQueries(resultSet.getInt("maxQueries"));
		user.setNotes(resultSet.getString("notes"));
		user.setCanDoND(resultSet.getBoolean("canDoND"));
		user.setCanDoNO(resultSet.getBoolean("canDoNO"));
		user.setCanDoSL(resultSet.getBoolean("canDoSL"));
		user.setCanDoUOPB(resultSet.getBoolean("canDoUOPB"));
		user.setQueriesCount(resultSet.getInt("queriesCount"));
		user.setCompany(resultSet.getString("company"));
		user.setAddress(resultSet.getString("address"));
		user.setCity(resultSet.getString("city"));
		user.setTelephone(resultSet.getString("telephone"));
		user.setCellPhone(resultSet.getString("cellPhone"));

		if (resultSet.getTimestamp("lastLogin") != null) {
			user.setLastLogin(new Date(resultSet.getTimestamp("lastLogin")
					.getTime()));
		}
		if (resultSet.getTimestamp("creationDate") != null) {
			user.setCreationDate(new Date(resultSet
					.getTimestamp("creationDate").getTime()));
		}
		user.setSessionHash(resultSet.getString("sessionHash"));
	}

	public boolean deleteUser(int id) {
		boolean result = false;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement("delete from users where id=?");
			statement.setInt(1, id);
			int deleted = statement.executeUpdate();
			result = deleted == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public boolean createNewUser(String email, String fullName,
			String password, Role role, String url, String username,
			String notes, boolean canDoNO, boolean canDoND, boolean canDoSL, boolean canDoUOPB,
			int maxQueries, String company, String address, String city,
			String telephone, String cellPhone) throws SQLException {
		boolean result = false;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("insert into users"
							+ "(username, password, fullName, role, email, url, notes, canDoNO, canDoND, canDoSL, canDoUOPB, maxQueries,company,address,city,telephone,cellPhone) "
							+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			statement.setString(1, username);
			statement.setString(2, password);
			statement.setString(3, fullName);
			statement.setString(4, role.toString());
			statement.setString(5, email);
			statement.setString(6, url);
			statement.setString(7, notes);
			statement.setBoolean(8, canDoNO);
			statement.setBoolean(9, canDoND);
			statement.setBoolean(10, canDoSL);
			statement.setBoolean(11, canDoUOPB);
			statement.setInt(12, maxQueries);
			statement.setString(13, company);
			statement.setString(14, address);
			statement.setString(15, city);
			statement.setString(16, telephone);
			statement.setString(17, cellPhone);
			int created = statement.executeUpdate();
			result = created == 1;
		} catch (SQLException e) {
			logger.error(e);
			if (e instanceof MySQLIntegrityConstraintViolationException) {
				throw e;
			}
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public boolean updateUser(int id, String email, String fullName,
			String password, Role role, String url, String username,
			String notes, int maxQueries, boolean canDoNO, boolean canDoND,
			boolean canDoSL, boolean canDoUOPB, String company, String address, String city,
			String telephone, String cellPhone) {
		boolean result = false;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("update users"
							+ " set username=?, password=?, fullName=?, email=?, url=?, "
							+ "notes=?, maxQueries=?, canDoNO=?, canDoND=?, canDoSL=?,canDoUOPB=? , company=?"
							+ " ,address=?,city=?,telephone=?,cellPhone=? "
							+ " where id=?");
			statement.setString(1, username);
			statement.setString(2, password);
			statement.setString(3, fullName);
			statement.setString(4, email);
			statement.setString(5, url);
			statement.setString(6, notes);
			statement.setInt(7, maxQueries);
			statement.setBoolean(8, canDoNO);
			statement.setBoolean(9, canDoND);
			statement.setBoolean(10, canDoSL);
			statement.setBoolean(11, canDoUOPB);
			statement.setString(12, company);
			statement.setString(13, address);
			statement.setString(14, city);
			statement.setString(15, telephone);
			statement.setString(16, cellPhone);
			statement.setInt(17, id);
			int created = statement.executeUpdate();
			result = created == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public boolean updateUserLoginTimeStamp(int id) {
		boolean result = false;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement("update users "
					+ "set lastLogin=CURRENT_TIMESTAMP" + " where id=?");
			statement.setInt(1, id);

			int created = statement.executeUpdate();
			result = created == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public boolean resetUsersQueriesCounters() {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement("update users "
					+ "set queriesCount=0");
			statement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return true;
	}

	public boolean incrementUserQueriesCount(int id) {
		boolean result = false;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement("update users "
					+ "SET queriesCount = queriesCount + 1 " + " where id=?");
			statement.setInt(1, id);
			int created = statement.executeUpdate();
			result = created == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public List<History> getUserHistory(User user) {
		List<History> result = getUserHistory(user.getId());
		for (History history : result) {
			history.setUser(user);
		}
		return result;
	}

	public List<History> getUserHistory(int userId) {
		List<History> result = new ArrayList<History>();
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("select * from history where userId=?");
			statement.setInt(1, userId);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				History history = new History();
				history.setId(resultSet.getInt("id"));
				history.setResults(resultSet.getInt("gotResults"));
				history.setSearchDate(new Date(resultSet.getTimestamp(
						"searchDate").getTime()));
				history.setSearchObject(resultSet.getString("searchObject"));
				history.setSearchQuery(resultSet.getString("searchQuery"));
				result.add(history);
			}
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public boolean changeUserPassword(String oldPassword, String newPassword,
			int userId) {
		boolean result = false;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement("update users"
					+ " set password=? where id=? and password=?");
			statement.setString(1, newPassword);
			statement.setInt(2, userId);
			statement.setString(3, oldPassword);
			int created = statement.executeUpdate();
			result = created == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}
}
